01 | 基础架构：一条SQL查询语句是如何执行的？
大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：
1 | mysql -h$ip -P$port -u$user -p |
一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置
全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。解决方案有两个
- 定期断开长连接。
- 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
查询缓存
连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存
但是大多数情况下我会建议你不要使用查询缓存
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
1 | mysql> select SQL_CACHE * from T where ID=10； |
需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了(彻底没有)
分析器
先做“词法分析”、再做“语法分析”
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。
02 | 日志系统：一条SQL更新语句是如何执行的？
只要我们写的是DML语句（insert,update,delete,create）等等，那么我们在数据库服务端执行的时候就会涉及到 redo log(重做日志) 和 binlog(归档日志) 两个日志文件的变动。
重要的日志模块：redo log
粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
并不是为了提升IO性能才设计的WAL。如果仅仅是为了提升性能，那为了WAL所采取的一系列措施也太得不偿失了。 WAL的出现是为了实现关系型数据库的原子性和持久性。实现原子性和持久性的最大困难是“写入磁盘”这个操作并不是原子性的，不仅有“写入”与“未写入”状态，还客观存在“正在写”的中间状态。 由于写入中间状态与崩溃都不可能消除，所以如果不做额外保障的话，将内存中的数据写入磁盘，并不能保证原子性与持久性。所以可能出现以下情形： 1：未提交事务，写入后崩溃（比如修改三个数据，程序还没修改完，但数据库已经将其中一个或两个数据的变动写入磁盘，此时出现崩溃） 2：已提交事务，写入前崩溃（程序已经修改完三个数据，但数据库还未将全部三个数据的变动都写入磁盘，此时出现崩溃） 由于写入中间状态与崩溃都是无法避免的，为了保证原子性和持久性，只能在崩溃恢复后采取补救措施，这种能力就被称为“崩溃恢复”。 为了能够实现崩溃恢复，采取了写日志的方式，写日志成功后再去写磁盘，这种事务实现方式被称为“提交日志（CommitLogging），目前阿里的OceanBase就采用这种方式，但是Commit Logging存在一个巨大缺陷：所有对数据的真实修改都必须发生在事务提交之后，即成功写入日志之后。在此之前，即使磁盘IO有足够的空闲，即使某个事务修改的数据量非常庞大，占用了大量的内存缓冲区，都不允许在事务提交前写入磁盘，因此这种方式对数据库性能的提升十分不利。 基于Commit Logging的问题就，提出了“提前写入日志”（Write-Ahead Logging）的日志改进方案，“提前写入”就是允许在事务提交之前写入变动数据的意思。而对于提前写入磁盘，在数据库崩溃后需要回滚的数据，给出的解决办法是增加另外一种被称为Undo Log的日志类型，当变动数据写入磁盘前，必须先记录Undo Log，以便在事务回滚或者崩溃恢复时根据Undo Log对提前写入的数据变动进行擦除。
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。【写redolog是顺序IO】
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写【某天赊账的特别多，粉板写满了，掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间】
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe
重要的日志模块：binlog
redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。为什么会有两份日志呢？
最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的。【InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。】
这两种日志有以下三点不同。
- redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
- redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
- redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
下面我们来看看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程
你可能注意到了，最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。
在事务的 Prepare 阶段，MySQL 将会向 Redo Log 中写入事务的 XID。这是为了在事务的 Commit 阶段，MySQL 可以根据 XID 来匹配 Binlog 中对应的内容保证一致性。
redo log 等待 binlog 写入完成后，由 prepare 变为 commit 提交状态【server层驱动，engine层负责存储相关暴露接口】
两阶段提交
通过定期的整库备份加上binlog的操作回放可以保证数据的安全性。因为binlog记录的是数据的逻辑操作（原始sql语句），而redolog是数据的物理操作日志，并且非innodb的引擎没有redolog。因此回放的时候回使用binlog进行回放
当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
- 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
- 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
如果不使用两阶段提交可能出现的问题：
- 先写 redo log 后写 binlog会导致丢失一次更新
- 先写 binlog 后写 redo log会导致恢复的时候多了一个事务出来
为什么“两阶段提交”可以解决这个风险？
真正的“两阶段提交” 是指对 redo log 进行“两阶段提交”：先 prepare，再commit。 数据库 crash-重启后，会对记录对redo log 进行check：
1、如果 redo log 已经commit，则视为有效。
2、如果 redo log prepare 但未commit，则check对应的bin log记录是否记录成功。
2.1、bin log记录成功则将该prepare状态的redo log视为有效
2.2、bin log记录不成功则将该prepare状态的redo log视为无效
不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
小结
redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失
innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。 0表示每秒将”log buffer”同步到”os buffer”且从”os buffer”刷到磁盘日志文件中。 1表示每事务提交都将”log buffer”同步到”os buffer”且从”os buffer”刷到磁盘日志文件中。 2表示每事务提交都将”log buffer”同步到”os buffer”但每秒才从”os buffer”刷到磁盘日志文件中。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。【redo log和binlog都是先写入内存buffer再落磁盘的】
课后提问
有一个问题，如果在非常极端的情况下，redo log被写满，而redo log涉及的事务均未提交，此时又有新的事务进来时，就要擦除redo log，这就意味着被修改的的脏页此时要被迫被flush到磁盘了，因为用来保证事务持久性的redo log就要消失了。但如若真的执行了这样的操作，数据就在被commit之前被持久化到磁盘中了。当真的遇到这样的恶劣情况时，mysql会如何处理呢，会直接报错吗？还是有什么应对的方法和策略呢
引用老师的话“这些数据在内存中是无效其他事务读不到的（读到了也放弃），同样的，即使写进磁盘，也没关系，再次读到内存以后，还是原来的逻辑”，就是会将redolog中日志刷到磁盘中，但是这些（注意是这些）尚未提交的事务，做的数据修改的版本，即使再次被加载内存中，对于其他事务（在读已提交，可重复读的隔离级别下，仍然是不可见）
与此同时，由于innodb引擎在同步刷盘，此时的事务需要发生等待
binlog还不能去掉。
一个原因是，redolog只有InnoDB有，别的引擎没有。
另一个原因是，redolog是循环写的，不持久保存，binlog的“归档”这个功能，redolog是不具备的。
03 | 事务隔离：为什么你改了我还看不见？
MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务
[!summary]
脏读:读到其他事务未提交的数据；
不可重复读：前后读取的记录内容不一致；
幻读：前后读取的记录数量不一致。
隔离性与隔离级别
- 读未提交(read uncommitted)是指，一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交(read committed)是指，一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读(repeatable read)是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
- 串行化(serializable)，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
- 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
- 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的
- 注意：“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念
- “串行化”隔离级别下直接用加锁的方式来避免并行访问。
Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。
配置的方式是，将启动参数 transaction-isolation【有些老版本是：tx_isolation】 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。
事务隔离的实现
我们展开说明“可重复读”。在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值【即除了记录变更记录，还会记录一条变更相反的回滚操作记录，前者记录在redo log，后者记录在undo log】
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。
在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）
什么时候删除回滚日志？—>不需要的时候—>当系统里没有比这个回滚日志更早的 read-view 的时候—>尽量不实用长事务(回滚记录占存储空间、占用锁资源)
在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。【清除的空间仅仅是可以分配给其他数据存储，不再被回滚段占用了，不会直接删除文件，类似于标记可覆盖】
事务的启动方式
MySQL 的事务启动方式有以下几种：
- 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
- set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。【注意这种情况select语句也会启动事务】
我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务
在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销
你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务
1 | select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60 |
问题：如何避免长事务对业务的影响？
首先，从应用开发端来看：
- 确认是否使用了 set autocommit=0。（一般设为1）
- 确认是否有不必要的只读事务。
- 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
其次，从数据库端来看：
- 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
- Percona 的 pt-kill 这个工具不错，推荐使用；
- 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
- 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。
04 | 深入浅出索引（上）
索引的常见模型
三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树
- 哈希表：碰撞后拉链、适用于只有等值查询的场景
- 有序数组：等值查询和范围查询场景中的性能就都非常优秀，但只适用于静态存储引擎(不方便中间插入数据)
- 二叉搜索树：查询和更新的时间复杂度都是 O(log(N))，更多使用的是N叉树
数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中
MySql默认一个节点的长度为16K，一个整数（bigint）字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针；所以16K/14B ≈ 1170【int是4位，4+6 = 10，根节点一页最多储存16k，除一下大约是1600条数据】
InnoDB 的索引模型
索引组织表：在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
根据叶子节点的内容，索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
回表：使用普通索引查询方式，需要先搜索索引数，得到ID值，再到ID索引数搜索一次 —> 尽量使用主键索引(基于非主键索引的查询需要多扫描一棵索引树)
索引维护
页分裂：插入数据的过程，所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去
注意：不是所有场景都一定要使用自增主键
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
从性能和存储空间方面考量，自增主键往往是更合理的选择。但有些场景适合用业务字段做主键：（典型的KV场景，例如身份证）
- 只有一个索引；
- 该索引必须是唯一索引。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
问题：通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理
1 | alter table T drop index k; |
索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB
其他：自增主键建议设置为bigint unsigned
05 | 深入浅出索引（下）
覆盖索引
select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。
例如可以在一个市民信息表上，将身份证号和名字建立联合索引。这样对于根据市民的身份证号查询他的姓名的高频请求，可以用到覆盖索引，并不需要回表查整行记录，记录语句的执行时间
最左前缀原则
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录
不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
在建立联合索引的时候，如何安排索引内的字段顺序？(索引的复用能力)
- 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
- 如果既有联合查询，又有基于 a、b 各自的查询，比如查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引
- 第二原则是空间。
- 比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
索引下推
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
例如对于联合索引 (name,age) ，查询语句是select * from tuser where name like ‘张 %’ and age=10 and ismale=1;
InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次
小结
- 高频查询，可以建立联合索引来使用覆盖索引，不用回表。
- 非高频查询，再已有的联合索引基础上，使用最左前缀原则来快速查询。
- 对于MySQL 5.6 引入索引下推，减少回表次数。
[!help]
按自己的理解，查询数据的大致流程是：
引擎内部表里的数据段／索引段取数据 ，数据是按照段->区->页维度去取 ， 取完后先放到数据缓冲池中，再通过二分法查询叶结点的有序链表数组找到行数据返回给用户 。 当数据量大的时候，会存在不同的区，取范围值的时候会到不同的区取页的数据返回用户。
06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类
全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。 也就是把整库每个表都 select 出来存成文本
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
一致性读是好，但前提是引擎要支持这个隔离级别。 比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。
所以，single-transaction 方法只适用于所有的表使用事务引擎的库。 如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法 —> DBA要求开发使用InnoDB 替代 MyISAM
你也许会问，既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
- 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用
- 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。 与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大
另一类表级的锁是 MDL（metadata lock)。 MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性
[!summary]
DDL（Data Definition Language）用于定义和管理数据库中的对象，包括表、视图、索引、约束等。
DML（Data Manipulation Language）用于对数据库中的数据进行操作，包括插入、更新、删除等。
DQL（Data Query Language）用于查询数据库中的数据，包括检索、过滤和排序等。
元数据锁MDL作用是防止DDL和DML并发的冲突。每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）
在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
MDL锁只会锁定元数据，不会对数据本身进行锁定，因此在进行数据操作时，仍然需要使用其他类型的锁来保证数据的正确性
- 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
基于上面的分析，我们来讨论一个问题，如何安全地给小表加字段？
- 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
- 热点表，kill不管用。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
[!info]
Online DDL的过程是这样的：
- 拿MDL写锁
- 降级成MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL锁
如果第四步升级为MDL写锁的时候，这个表的MDL锁有其他事务占着，那么这个事务会阻塞，等到可以拿到MDL写锁，如果不幸一直拿不到，最后锁超时了，就只好回滚这个DD操作了
07 | 行锁功过：怎么减少行锁对性能的影响？
注意：MyISAM不支持行锁（所以 MyISAM 不适合高并发的场景。 它更适合读多写少的场景）
从两阶段锁说起
两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
举一个场景例子，假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。简化后的业务是
- 从顾客 A 账户余额中扣除电影票价；
- 给影院 B 的账户余额增加这张电影票价；
- 记录一条交易日志。
因为操作2要更新同一个影院账户的余额，需要修改同一行数据，所以如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度（不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的）
死锁和死锁检测
当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。（在InnoDB中的默认值是50s，在线服务无法接受；但舍得太短容易误伤，所以基本不使用）
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。（这种主动检测的方式，对于热点行更新的场景消耗大量的CPU资源）
整体死锁检测的耗费代价是 O(n2)级别的。 并发更新同一行的1000个线程，整体耗费的死锁检测操作为1000*1000=100万。这个时间复杂度是1+2+3+…+n，也就是1/2*n*(n+1)，对于时间复杂度也是O(n^2)。
怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU 资源
- 一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉（关掉死锁检测意味着可能会出现大量的超时，这是业务有损的）【即锁超时策略，默认50s】
- 另一个思路是控制并发度。在客户端做并发控制不可行，并发数不会小。并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了【即在proxy层做并发控制、请求排队】
如果没有数据库专家，优化时可以考虑通过将一行改成逻辑上的多行来减少锁冲突。例如将影院的账户总额分到10条，在加金额时随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数【有点像java的分段锁】
这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。
总结本段的内容就是：
高并发下避免死锁检测带来的负面影响： 1. 确保业务上不会产生死锁，直接将死锁检测关闭。（innodb 自带死锁检测） 2. 在数据库中间件中统一对更新同一行的请求进行排队，控制并发度。 3. 业务逻辑上进行优化，将一行数据分解成多行，降低写入压力。
课后问题
[!question]
如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。你会选择哪一种方法呢？为什么呢？
第二种方式是相对较好的。
第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。
[!question]
上一节讲的dml时会产生读MDL锁（表锁），也就是update会持有读MDL。读和读不互斥。但是对于行锁来说。两个update同时更新一条数据是互斥的。这个是因为多种锁同时存在时，以粒度最小的锁为准的原因么？
不是“以粒度最小为准” 而是如果有多种锁，必须得“全部不互斥”才能并行，只要有一个互斥，就得等。
[!question]
死锁检测innodb_deadlock_detect是每条事务执行前都会进行检测吗？如果是这样，即使简单的更新单个表的语句，当每秒的并发量达到上千的话，岂不是也会消耗大量资源用于死锁检测吗？
如果他要加锁访问的行上有锁，他才要检测。 这里面我担心你有两个误解，说明下：
- 一致性读不会加锁，就不需要做死锁检测；
- 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：
B在等A， D在等C， 现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A
其他零碎
- show engine innodb status; 可以用这个命令看死锁日志（持续监控，发现新的就存起来）
- reset_connection只是复位状态，恢复到连接和权限验证之后的状态，没有重连
- 关于innodb的行级锁，在更新数据时：
- 如果隔离级别是rr的话，那就说明你不能出现不可重复读，所以当你在用没有索引的字段当where条件更新的话，并且没有加limit，那么mysql会扫描主键然后锁住所有的行，因为他就是要保证他在更新的时候，别的事务不能搞事情啊，要别人插入一条，或者更新了一条，恰好又满足我刚才那个where条件 ，那就违反了当前的隔离级别了，当然rc条件下，就无所谓了，反正我的隔离级别下会出现不可重复读，别人要要更新那请便，只要不搞我当前扫描到这行就行了（因为他加了行锁）。加了limit 亦然，rr下，我扫描到的这前多少行你不能给我搞事情，搞了就违反我的隔离级别，所以他锁住了前多少行，后面爱咋搞咋搞。rc也无所谓，也只是行锁，锁住那几行。
08 | 事务到底是隔离的还是不隔离的？
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。【思考：数据库在设计的时候，一定会考虑最大程度的支持事务之间的并发，那它一定会让锁的时间尽可能短】
第一种启动方式，一致性视图是在第执行第一个快照读语句时创建的；
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。
在 MySQL 里，有两个“视图”的概念：
- 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
- 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。【通过高低水位，数据版本号，undo日记来进行判断数据可见不可见，达到MVCC目的】
“快照”在 MVCC 里是怎么工作的？
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。
图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。【事务更新数据后，会把自己的事务ID赋值给该数据版本，记为row trx_id】
图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。
当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。
这个视图数组把所有的 row trx_id 分成了几种不同的情况。
对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：
- 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
- 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
- 如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
之后的更新，生成的版本一定属于上面的 2 或者 3(a) 的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。
高水位的定义是事务创建时所有未提交的事务ID的最大值+1是高水位，但并不是小于高水位大于低水位的事务就都没有提交。所以row trx_id 在这个范围内却不在数组中就是已经提交了的可见
InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
我来给你翻译一下。一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
- 版本未提交，不可见；
- 版本已提交，但是是在视图创建后提交的，不可见；
- 版本已提交，而且是在视图创建前提交的，可见。
更新逻辑
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。【其实就是针对buffer pool直接进行修改。 此时的update 为什么能够进行当前读呢，是依靠的行锁。 当前读保证读到的是已经提交的，因为行锁是两阶段锁。】
其实，除了 update 语句外，select 语句如果加锁，也是当前读。即查询语句加上 lock in share mode(读锁：S 锁，共享锁) 或 for update(写锁：X 锁，排他锁)【显示加锁是当前读，即读取诗句的新版本】
回到文章开头的问题：事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
- 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
- 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。
小结
InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
- 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
- 对于读提交，查询只承认在语句启动前就已经提交完成的数据；
而当前读，总是读取已经提交完成的最新版本。
表结构只能遵循当前读，因为表结构没有对应的行数据，也没有 row trx_id
当然，MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可重复读。
课后问题
[!question]
当开启事务时，需要保存活跃事务的数组（A），然后获取高水位（B）。我的疑问就是，在这两个动作之间（A和B之间）会不会产生新的事务？如果产生了新的事务，那么这个新的事务相对于当前事务就是可见的，不管有没有提交。
代码实现上，获取视图数组和高水位是在事务系统的锁保护下做的，可以认为是原子操作，期间不能创建事务。
